package dbv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Uatre {

	/**
	 * @param args
	 */
	
/*	
	String strcon= "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + sitiobase;

	try {

	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
*/
	public ResultSet datosUatre(String periodo){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/huaicos", "", ""); 
			String sql = "SELECT empleado.CUIL, " +
     		"SUM(liquidac.REM_APORTE) AS importe FROM " +
     		"huaicos.empleado INNER JOIN huaicos.liquidac  " +
     		"ON (empleado.LEGAJO = liquidac.LEGAJO) " +
     		"WHERE (liquidac.PERIODO = ? and empleado.cuil <> '20-16370604-1') " +
     		"GROUP BY empleado.LEGAJO, liquidac.PERIODO";
			
	        PreparedStatement ps = conexion.prepareStatement(sql);
	        ps.setString(1, periodo);
	        ps.execute();
	        
	        return ps.getResultSet();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;

	}
	
	public ResultSet datosUatre(String periodo, String base){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String sql = "SELECT empleado.CUIL, " +
     		"SUM(DISTINCT liquidac.REM_APORTE) AS importe FROM " +
     		base+".empleado INNER JOIN "+base+".liquidac  " +
     		"ON (empleado.LEGAJO = liquidac.LEGAJO) " +
     		"WHERE (liquidac.PERIODO = ?) " +
     		"GROUP BY empleado.LEGAJO, liquidac.PERIODO";
			
	        PreparedStatement ps = conexion.prepareStatement(sql);
	        ps.setString(1, periodo);
	        ps.execute();
	       
	        return ps.getResultSet();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;

	}
	

	
	
	
	public void actualizaUatre(ResultSet rs){
		try {
			 String userName = "";
	         String password = "24909558leandro25086";
	         String url = "jdbc:odbc:baseUatre";
	         Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	         Connection conn = DriverManager.getConnection (url, userName, password);
	         String sql = "update view_trabajador set remuneracion = ?  where cuil = ?";
			 

			 PreparedStatement ps2 = conn.prepareStatement(sql);
	        
			 rs.beforeFirst();			 
			 while (rs.next()){
		         ps2.setBigDecimal(1, rs.getBigDecimal(2));
				 ps2.setString(2, rs.getString(1));
				 ps2.executeUpdate();
	         }
			rs.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	


	
	

	
}
